﻿create procedure dbo.GetCategoryMenus
(
		@CategoryID int
	)

AS
	SET NOCOUNT ON
	 
	-- Category
	SELECT     Id, Name, Description
	FROM        dbo.Category
	WHERE     (Id = @CategoryID)
	
	-- Menus
	
	
	SELECT     Id, CategoryId, Name, Address, City, State, Zipcode, TaxRate
	FROM         dbo.Menus
	WHERE     (CategoryId = @CategoryID)
	-- Menu Items
	
	SELECT    dbo.MenuItems.Id, dbo.MenuItems.MenusId, dbo.MenuItems.Description, dbo.MenuItems.Price, dbo.MenuItems.PrepTimeMinutes
	FROM         dbo.Menus INNER JOIN
	                      dbo.MenuItems ON dbo.Menus.Id = dbo.MenuItems.MenusId
	WHERE     (dbo.Menus.CategoryId = @CategoryID)
	
	-- Preferred TakeoutMenu
	
	SELECT     dbo.PreferredTakeoutMenu.Id, dbo.PreferredTakeoutMenu.Name, dbo.PreferredTakeoutMenu.UserID, dbo.PreferredTakeoutMenu.Instructions
	FROM         dbo.PreferredTakeoutMenuItems INNER JOIN
	                      dbo.PreferredTakeoutMenu ON dbo.PreferredTakeoutMenuItems.PreferredTakeoutMenuId = dbo.PreferredTakeoutMenu.Id INNER JOIN
	                      dbo.Menus INNER JOIN
	                      dbo.MenuItems ON dbo.Menus.Id = dbo.MenuItems.MenusId ON dbo.PreferredTakeoutMenuItems.MenuItemID = dbo.MenuItems.Id
	WHERE     (dbo.Menus.CategoryId = @CategoryID)
	
	-- Preferred TakeoutMenu Items
	 
	 SELECT     dbo.PreferredTakeoutMenuItems.Id, dbo.PreferredTakeoutMenuItems.MenuItemID, dbo.PreferredTakeoutMenuItems.PreferredTakeoutMenuId, 
	                       dbo.PreferredTakeoutMenuItems.Quantity
	 FROM         dbo.PreferredTakeoutMenuItems INNER JOIN
	                       dbo.Menus INNER JOIN
	                       dbo.MenuItems ON dbo.Menus.Id = dbo.MenuItems.MenusId ON dbo.PreferredTakeoutMenuItems.MenuItemID = dbo.MenuItems.Id
	 WHERE     (dbo.Menus.CategoryId = @CategoryID)
	 
	RETURN


